package com.xzd.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.Region;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.xzd.common.style.excel.HSSFCustomDefaultStyle;
import com.xzd.common.style.excel.HSSFCustomStyle;



/**
 * 导出电子表格
 * 
 */
public class ExcelUtils {
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFPatriarch patriarch;

	private HSSFCustomStyle customStyle = new HSSFCustomDefaultStyle();

	private FileOutputStream fos;

	private String sheetName = "first"; // 工作表名
	private int[] columnWidth; 			// 列的宽度

	private void init() {
		wb = new HSSFWorkbook();
		if(customStyle != null) {
			customStyle.setWb(wb);
		}
		sheet = wb.createSheet(sheetName);
		patriarch = sheet.createDrawingPatriarch();
		
	}
	
	
	
	public ExcelUtils() {
		init();
	}

	public ExcelUtils(String sheetName, HSSFCustomStyle customStyle) {
		this.sheetName = sheetName;
		this.customStyle = customStyle;
		init();
	}
	
	/**
	 * 读取一个列表
	 * @param sheetIndex
	 * @return
	 */
	public List<String[]> readTable(int sheetIndex) {
		
		List<String[]> list = new ArrayList<String[]>();
		sheet = wb.getSheetAt(sheetIndex);
		HSSFRow row = null;
		String[] strArray;
		int lastCellNum = 0;
		
		for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
			
			row = sheet.getRow(j);
			if(row == null) {
				break;
			}
			
			if(j==0) {
				lastCellNum = row.getLastCellNum();
			}
			
			strArray = new String[lastCellNum];
			
			for(int i=0; i<lastCellNum; i++) {
				strArray[i] = readCell(row.getCell(i));
			}
			
			list.add(strArray);
		}
		return list;
	}
	
	public void addTable(String firstTitle,Object[] titleRow, List<Object[]> contentRows) {
		HSSFRow firstRow = this.createRow();	
		HSSFCell firstCell = this.createCell(firstRow);;
		firstCell.setCellValue(new HSSFRichTextString(firstTitle));
		firstCell.setCellStyle(customStyle.getGridTitleCellStyle());
		sheet.addMergedRegion( new Region(0,(short)0,0,(short)(titleRow.length-1) ));
		sheet.autoSizeColumn(0);
		
		HSSFRow row = this.createRow();		
		HSSFCell cell = null;
		for (int i = 0; i < titleRow.length; i++) {
			cell = this.createCell(row);

			cell.setCellStyle(customStyle.getGridTitleCellStyle());
			cell.setCellValue(new HSSFRichTextString(titleRow[i].toString()));
		}

		for (int i = 0; i < contentRows.size(); i++) {
			row = this.createRow();

			Object[] contentArray = contentRows.get(i);

			for (int j = 0; j < contentArray.length; j++) {
				cell = this.createCell(row);
				cell.setCellStyle(customStyle.getGridContentCellStyle());	
				String tempString = null;
				try {				
					if(contentArray[j] != null) {
						tempString = contentArray[j].toString();
					}
					cell.setCellValue(Integer.parseInt(tempString));
				} catch (NumberFormatException e) {
					cell.setCellValue(new HSSFRichTextString(tempString));
				}

			}

		}

	}

	/**
	 * 自动调整列宽 默认调整10列
	 */
	public void autoColumn() {
		
		for (short i = 0; i < 22; i++) {
			sheet.autoSizeColumn(i);
		}
	}
	
	/**
	 * 向excel添加一张图片
	 */
	public void addPicture(byte[] pictureData, int format) {

		HSSFRow row = createRow();
		row.setHeight((short) (300 * 15));

		HSSFClientAnchor anchor = new HSSFClientAnchor();
		anchor.setRow1(sheet.getLastRowNum());
		patriarch.createPicture(anchor, wb.addPicture(pictureData, format))
				.resize();

	}

	/**
	 * 添加一行空行
	 */
	public void addBlankRow() {
		HSSFRow row = createRow();
		HSSFCell cell = this.createCell(row);

		sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row
				.getRowNum(), cell.getColumnIndex(), 9));
	}

	public void addMainTitleRow(String content) {
		addTitleRow(content, customStyle.getMainTitleCellStyle());
	}

	public void addTwoLevelTitleRow(String content) {
		addTitleRow(content, customStyle.getTwoLevelTitleCellStyle());
	}

	public void addThreeLevelTitleRow(String content) {
		addTitleRow(content, customStyle.getThreeLevelTitleCellStyle());
	}
	
	public void addTitleRow(String content, HSSFCellStyle sytle) {
		HSSFRow row = createRow();

		HSSFCell cell = this.createCell(row);
		cell.setCellStyle(sytle);
		cell.setCellValue(new HSSFRichTextString(content));

		sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row
				.getRowNum(), cell.getColumnIndex(), 9));
	}

	private HSSFRow createRow(int rownum) {
		return sheet.createRow(rownum);
	}

	private HSSFRow createRow() {
		int rownum = 0;
		if (sheet.getPhysicalNumberOfRows() > 0) {
			rownum = sheet.getPhysicalNumberOfRows();
		}

		return this.createRow(rownum);
	}

	private HSSFCell createCell(HSSFRow row, int cellNum) {
		return row.createCell(cellNum);
	}

	private HSSFCell createCell(HSSFRow row) {
		int cellNum = 0;
		if (row.getPhysicalNumberOfCells() > 0) {
			cellNum = row.getPhysicalNumberOfCells();
		}

		return this.createCell(row, cellNum);
	}

	private String readCell(HSSFCell cell) {

		String cellvalue = null;
		if (cell == null)
			return cellvalue;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_BLANK:
			cellvalue = null;
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				cellvalue = cell.getDateCellValue().toString();
			}else{   
		    	cellvalue = (int) cell.getNumericCellValue() + ""; 
            }  
			break;
		case HSSFCell.CELL_TYPE_STRING:
			cellvalue = cell.getRichStringCellValue() + "";
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			switch (cell.getCachedFormulaResultType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellvalue = cell.getRichStringCellValue() + "";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				cellvalue = (int) cell.getNumericCellValue() + "";
				break;
			default:
			}
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellvalue = cell.getBooleanCellValue() + "";
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			cellvalue = cell.getErrorCellValue() + "";
			break;
		default:
		}
		return cellvalue;
	}
	
	public void removeSheetAt(int index) {
		wb.removeSheetAt(index);
	}
	
	public void createSheet(String sheetName) {
		sheet = wb.createSheet(sheetName);
	}

	
	public void export(HttpServletRequest request, HttpServletResponse response, String fileName) {
		
		 try {

			fileName=new String(fileName.getBytes("GB2312"),"ISO_8859_1");
			response.setHeader("Content-disposition", "attachment; filename=" + fileName);
//			response.setContentLength((int) exportFile.length());
			response.setContentType("application/x-download");
			
			
			wb.write(response.getOutputStream());
			
//		     FileInputStream fis = new FileInputStream(exportFile);
		    
		     
//		     PrintWriter out = response.getWriter();
//			 int i;
//			 while ((i = fis.read()) != -1) {
//				out.write(i);
//			 }
//			 
//			 fis.close();
//			 out.close();

		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void generate(String path, String fileName) {
		try {
			fos = new FileOutputStream(path + fileName);
			
			wb.write(fos);

		} catch (Exception e) {
			System.out.println("生成excel文件失败");

			try {
				fos.flush();
				fos.close();
			} catch (Exception e1) {
				System.out.println("生成excel时 关闭FileOutputStream失败！！");
			}

			e.printStackTrace();
		}

		System.out.println("生成" + path + fileName + "文件成功！！");
	}
	
	public void download(HttpServletResponse response, String path, String fileName) {
		FileInputStream fis;
		
		 try {
			
			 fileName=new String(fileName.getBytes("UTF-8"),"ISO_8859_1");
			
			String allPathFileName = path + fileName;
			
			File exportFile = new File(allPathFileName);

			response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"),"ISO_8859_1"));
			response.setContentLength((int) exportFile.length());
			response.setContentType("application/x-download");

		     fis = new FileInputStream(exportFile);
		    
		     PrintWriter out = response.getWriter();
			 int i;
			 while ((i = fis.read()) != -1) {
				out.write(i);
			 }
			 fis.close();
			 out.close();

		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	

	/**
	 * 设置导入文件
	 * @param importFile
	 * @throws Exception
	 */
	public void setImportFile(File importFile) throws Exception {
		POIFSFileSystem poiFile = new POIFSFileSystem(new FileInputStream(
				importFile));
		wb = new HSSFWorkbook(poiFile);
	}
	
	
	/**
	 * 导入excel
	 * @param filePath
	 * @param model
	 * @return
	 */
	public List<Map> readExcel(String filePath,String[] model){
		// 创建Excel文件的工作薄引用
		System.out.println("开始导入");
		 List<Map> xslList=new ArrayList<Map>();
		 Sheet sheet;
		 Workbook wb = null;
			try {
				POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));   
				wb = new HSSFWorkbook(fs);
				sheet = wb.getSheetAt(0);
			} catch (Exception e) {
				try {
					wb = new XSSFWorkbook(new FileInputStream(filePath));
				} catch (Exception e1) {
					e1.printStackTrace();
				} 
				sheet = wb.getSheetAt(0);  
			}  
			
		   try {     
			   
	            Map rowMap=null;
	            int rowNum=0;
	            DecimalFormat numFormat = new DecimalFormat("#.##");
	            for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {    //迭代行   
	                Row row = (Row) rit.next();   
	                int i = 0;   
	                rowMap=new HashMap();
	                
	                for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {//迭代列  
	                    Cell cell = (Cell)cit.next();   
	                    String strCell = ""; 
	                    
	                    switch (cell.getCellType()) {   
	                        case HSSFCell.CELL_TYPE_NUMERIC: 
	                        	strCell = numFormat.format((cell.getNumericCellValue()));     
	                            break;     
	                        case HSSFCell.CELL_TYPE_STRING: strCell = cell.getStringCellValue().trim();     
	                            break;     
	                        case HSSFCell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue());     
	                            break;  
	                        case HSSFCell.CELL_TYPE_BLANK:
	                        	strCell="";
	                        	break;
	                        default: strCell = "";     
	                            break;     
	                    }   
	                    //根据不同的模板配置,当第一行时不读取
	                    if(rowNum>0 && !strCell.trim().equals("")){
	                    	rowMap.put(model[i], strCell);
	 	               }  
	                    i++;   
	                }   
	                //System.out.println();
	                if(rowNum>0 ) {
	                	xslList.add(rowMap);
	                }
	                
	                rowNum++;
	            }   
	        } catch (Exception e) {   
	            e.printStackTrace();   
	        }   
	        return xslList;

	}
	

	public HSSFWorkbook getWb() {
		return wb;
	}

	public void setWb(HSSFWorkbook wb) {
		this.wb = wb;
	}

	public HSSFSheet getSheet() {
		return sheet;
	}

	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public FileOutputStream getFos() {
		return fos;
	}

	public void setFos(FileOutputStream fos) {
		this.fos = fos;
	}

	public int[] getColumnWidth() {
		return columnWidth;
	}

	public void setColumnWidth(int[] columnWidth) {
		this.columnWidth = columnWidth;
	}

}
